@using System;
@using WebMatrix.Data;

@functions {
    // Data access class for the blog wired into the sql compact database

    static string Datasource ="Blog";

    static Database Connection ;

    // open a connection
    static void Open(){
        Connection = Database.Open(Datasource);
    }
    
    // close a connection to the database;
    static void Close(){
        Connection.Close();
    }
    
    // ARTICLES
    
    public static dynamic GetAllArticles(string OrderBy=null,string SortDir=null){
        Open();
        string Order,Sort;
        switch(OrderBy){
            case "title":
                Order = "title";
                break;
            case "category":
                Order = "categoryId";
                break;
         
            default:
                Order = "CreationDate";
                break;
        }
        switch(SortDir){
            case "DESC":
                Sort = "DESC";
                break;
            default:
                Sort = "ASC";
                break;
        }
        var Result = Connection.Query(@"SELECT article.id , title , summary , creationDate ,
                published , category.id AS categoryId ,
                category.name AS categoryName FROM Article INNER JOIN Category
                ON Article.categoryId = Category.Id ORDER BY "+Order+" "+Sort);
        Close();
        return Result;
    }
    
    public static dynamic GetPublishedArticles(dynamic category){
        Open();
        var Result = Connection.Query("SELECT * FROM Article WHERE published=@0 AND categoryId=@1 ORDER BY creationDate DESC",1,category);
        Close();
        return Result;
    }
    
    public static int AddArticle(string title,string summary,string content,
    string categoryId,string authorId,string published,string enableComments,string showComments){
        Open();
        try{
            var Result = Connection.Execute(@"INSERT INTO Article(title,
                summary,content,categoryId,authorId,updateDate,published,enableComments,ShowComments) 
                values(@0,@1,@2,@3,@4,@5,@6,@7,@8)",
            title,summary,content,categoryId,authorId,DateTime.Now,published,enableComments,showComments);
            return Result;
        }catch(Exception ex){
            return -1;
        }finally{
           Close();
        }
        
    }

    public static int DeleteArticle(string id,string authorId){
        Open();
        try{
            var Result = Connection.Execute(@"DELETE FROM Article 
            WHERE id = @0 AND authorId = @1 ",id,authorId);
            return Result;
        }catch(Exception ex){
            return -1;
        }finally{
            Close();
        }
    }
    
    public static int EditArticle(string title,string summary,
    string content,string categoryId,string authorId,string id,string published,string enableComments,string showComments,string creationDate){
        Open();
        try{
            var Result = Connection.Execute(@"UPDATE Article SET title=@0, 
                summary=@1, content=@2 ,categoryId=@3,updateDate=@4,published=@5,enableComments=@6,ShowComments=@7,creationDate=@8
                WHERE authorId=@9 AND id=@10 ",
                title,summary,content,categoryId,DateTime.Now,published,enableComments,showComments,creationDate,authorId,id) ;
            return Result;
        }catch(Exception ex){
            //throw ex;
                     return -1;
        }finally{
           Close();
        }
        
    }
    
    public static dynamic GetArticleByAuthorAndId(string articleId,string authorId){
        Open();
        try{
            var Result = Connection.QuerySingle(@"SELECT * FROM Article WHERE id=@0 AND authorId=@1",
                articleId,authorId);
            return Result;
        }catch(Exception ex){
            throw ex;
        }finally{
            Close();
        }
    }
    
    public static int ChangeArticlePublishedState(string articleId,string authorId,string state){
        Open();
        try{
            var Result = Connection.Execute(@"UPDATE Article SET published=@0 WHERE id=@1 AND authorId=@2",
                state,articleId,authorId);
                return Result;
        }catch(Exception ex){
            throw ex;
        }finally{
            Close();
        }
    }
    
    public static dynamic GetPublishedArticleById(string articleId){
        Open();
        var Result = Connection.QuerySingle(@"SELECT * FROM Article INNER JOIN
            UserProfile ON Article.authorId = UserProfile.UserId WHERE id=@0 
            AND published=@1 ORDER BY creationDate ASC ",articleId,1);
        Close();
        return Result;
        
    }
    
    public static dynamic GetArticleById(string articleId){
        Open();
        var Result = Connection.QuerySingle(@"SELECT * FROM Article INNER JOIN 
            UserProfile ON Article.authorId = UserProfile.UserId WHERE Article.id=@0 ",articleId);
        Close();
        return Result;
        
    }
    
    // CATEGORIES
    
    public static dynamic GetFirstCategoryId(){
        Open();
		var Result = Connection.QueryValue("select top(1) id from Category");
        Close();
        return Result;
    }
    
    public static dynamic GetCategories(){
        Open();
        var Result = Connection.Query("SELECT * FROM Category");
        Close();
        return Result;
    }
    
    // COMMENTS
    
    public static dynamic GetAllComments(){
        Open();
        var Results = Connection.Query(@"SELECT Comment.id, Comment.title,Comment.author,
            Comment.email,Comment.message,Comment.creationDate,
            Comment.published,Article.title AS ArticleTitle, UserProfile.DisplayName , UserProfile.UserId AS RegisteredUser FROM  Comment
            INNER JOIN Article ON Article.id = Comment.ArticleId 
            LEFT JOIN UserProfile ON UserProfile.UserId = Comment.userId");
        Close();
        return Results;
    }
    
    public static dynamic GetAllCommentsByArticleId(string articleId){
        Open();
        var Results = Connection.Query("SELECT * FROM Comment WHERE ArticleId=@0",articleId);
        Close();
        return Results;
    }
    
    
    public static dynamic GetPublishedCommentsByArticleId(string ArticleId){
        Open();
        var Results = Connection.Query("SELECT * FROM Comment WHERE ArticleId=@0 AND published=1", ArticleId);
        Close();
        return Results;
    }
    
    public static dynamic GetCommentByArticleIdAndUserId(string UserId, string CommentId){
        Open();
        var Result = Connection.QuerySingle(@"SELECT * FROM Comment
            INNER JOIN Article ON Comment.ArticleId = Article.id 
            AND Article.authorId = @0 
            AND Comment.id = @1 ",UserId,CommentId);
        Close();
        return Result;
    }
    
    public static int AddComment(string name,string title,string email,string message,string website,string articleId,string userId,string published)
    {
        Open();
        var Result = Connection.Execute(@"INSERT INTO Comment(author,title,email,message,website,articleId,userId,published,creationDate)
            values(@0,@1,@2,@3,@4,@5,@6,@7,@8)",name,title,email,message,website,articleId,userId,published,DateTime.Now);
        Close();
        return Result;
    }
    
    public static int DeleteCommentById(string CommentId){
        Open();
        var Result = Connection.Execute("DELETE FROM Comment WHERE Comment.id = @0",CommentId);
        Close();
        return Result;
    }
    
    public static int PublishComment(string CommentId)
    {
        Open();
        var Result = Connection.Execute("UPDATE Comment SET published = 1 WHERE Comment.id=@0",CommentId);
        Close();
        return Result;
    }

    public static int UnPublishComment(string CommentId)
    {
        Open();
        var Result = Connection.Execute("UPDATE Comment SET published = 0 WHERE Comment.id=@0",CommentId);
        Close();
        return Result;
    }   
    // USER PROFILES
    
    public static dynamic GetUserProfiles(){
        Open();
        var Result = Connection.Query("SELECT * FROM UserProfile");
        Close();
        return Result;
    }
    
    public static dynamic GetCurrentUserProfile(string UserId){
        Open();
        var Result = Connection.QuerySingle(@"SELECT UserId,     
            DisplayName, Email , Bio FROM UserProfile WHERE
            UserId=@0",UserId);
        Close();
        return Result;
    }
    
    public static dynamic UpdateUserProfileByUserId(string UserId,string DisplayName,string Email,string Bio){
        Open();
        var Result = Connection.Execute(@"UPDATE UserProfile SET DisplayName=@0, Email=@1, Bio=@2
            WHERE UserId=@3",DisplayName,Email,Bio,UserId);
        Close();
        return Result;
    }
        
}